package main.java.org.punch.dbtqv;

/**
 * Author: Fami
 * File: excel.java
 * ver: 1.0
 * Desc: Class to write/read the set of data Object to/from Excel.
 */


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class excel {
	
	private static XSSFSheet sheet = null;
	private static XSSFWorkbook workbook = null;
	private static XSSFCellStyle cellinfoTableStyle =null;
	private static XSSFCellStyle cellDataLabelStyle =null;
	private static XSSFCellStyle cellDataStyle =null;
	private static FileOutputStream ouputStream =null;
	
	
	//0.0 EXCEL WRITER
	public static String excelWriter (String srcPath, String excelFileName, String tabName, 
			Map<String, Object[]> infoTable, Map<String, Object[]> dataHeader, Map<String, Object[]> data){
		
		try {
			workbook = new XSSFWorkbook();
			cellinfoTableStyle = workbook.createCellStyle();
			cellDataLabelStyle = workbook.createCellStyle();
			cellDataStyle = workbook.createCellStyle();
			
			// 1.0 Create font type 
			XSSFFont infoTableFont = workbook.createFont();
			infoTableFont.setFontHeightInPoints((short)12);
			infoTableFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
			infoTableFont.setColor(HSSFColor.BLUE.index);
			
			XSSFFont tableHeadFont = workbook.createFont();
			tableHeadFont.setFontHeightInPoints((short)12);
			tableHeadFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
			tableHeadFont.setColor(HSSFColor.BROWN.index);
			
			XSSFFont tableDataFont = workbook.createFont();
			tableDataFont.setFontHeightInPoints((short)12);
			tableDataFont.setColor(HSSFColor.BLACK.index);
		
			// 2.0 Get the sheet
			sheet = workbook.createSheet(tabName);
			
			// 3.0 Create font styles
			cellinfoTableStyle.setFont(infoTableFont);
			cellinfoTableStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
			cellinfoTableStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
			cellinfoTableStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
			cellinfoTableStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
			
			cellDataLabelStyle.setFont(tableHeadFont);
			cellDataLabelStyle.setBorderTop(XSSFCellStyle.BORDER_THICK);
			cellDataLabelStyle.setBorderLeft(XSSFCellStyle.BORDER_THICK);
			cellDataLabelStyle.setBorderRight(XSSFCellStyle.BORDER_THICK);
			cellDataLabelStyle.setBorderBottom(XSSFCellStyle.BORDER_THICK);
			 
			cellDataStyle.setFont(tableDataFont);
			cellDataStyle.setFont(tableDataFont);
			cellDataStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
			cellDataStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);

			// 4.0 Info table write to excel
			Set<String> keyset = infoTable.keySet();
			int rownum = 1 ; 
		
			for(String key : keyset){
				Row row = sheet.createRow(rownum++);
				Object [] objarr = infoTable.get(key);
				int cellnum = 1; 
				
				for (Object obj : objarr){
					sheet.autoSizeColumn(cellnum);   		
					Cell cell = row.createCell(cellnum++);
					cell.setCellStyle(cellinfoTableStyle);
					
					if(obj instanceof String)
						cell.setCellValue((String)obj);
					else if (obj instanceof Integer)
						cell.setCellValue((Integer)obj);
				}
			}
			
			// 5.0 Data header write to excel
			Set<String> keyset2 = dataHeader.keySet();
			int rownum2 = 6 ; 
					
			for(String key : keyset2){
				Row row = sheet.createRow(rownum2++);
				Object [] objarr = dataHeader.get(key);
				int cellnum = 1; 
				
				for (Object obj : objarr){
					sheet.autoSizeColumn(cellnum);   		
					Cell cell = row.createCell(cellnum++);
					cell.setCellStyle(cellDataLabelStyle);
					
					if(obj instanceof String)
						cell.setCellValue((String)obj);
					else if (obj instanceof Integer)
						cell.setCellValue((Integer)obj);
				}
			}
											
			// 6.0 Data write to excel
			Set<String> keyset3 = data.keySet();
			int rownum3 = 8 ; 
					
			for(String key : keyset3){
				Row row = sheet.createRow(rownum3++);
				Object [] objarr = data.get(key);
				int cellnum = 1; 
				
				for (Object obj : objarr){
					sheet.autoSizeColumn(cellnum);   		
					Cell cell = row.createCell(cellnum++);
					cell.setCellStyle(cellDataStyle);
					
					if(obj instanceof String)
						cell.setCellValue((String)obj);
					else if (obj instanceof Integer)
						cell.setCellValue((Integer)obj);
				}
			}
									
			// 7.0 File output stream
			try{
				ouputStream = new FileOutputStream(new File (srcPath+excelFileName)); 
				workbook.write(ouputStream);
				ouputStream.close();
				System.out.println("INFO: "+excelFileName+" succesfully write out!!");
				
			}catch (Exception e){
				System.out.println("FAIL: Excel write out fail due to: ");
				e.printStackTrace();
			}
			
		// 8.0 Exception to cater the main method failure
		} catch (Exception e){
			System.out.println("[FAILURE] Excel Writer fail due to: ");
			e.printStackTrace();
		}
		return "pass";
		
	}
	
	
	//1.0 READING THE EXCEL SPREADSHEET
	public static void read(String srcPath, String excelFile){
		try{
			FileInputStream file = new FileInputStream(srcPath+excelFile); 
			//Get the spreadsheet instance
			XSSFWorkbook workbook= new XSSFWorkbook(file);
			// get first sheet from workbook
			XSSFSheet sheet = workbook.getSheetAt(0);
			// Iterator to get all row in current shit
			Iterator<Row> rowIterator = sheet.iterator();
			
			while ( rowIterator.hasNext()){
				Row row = rowIterator.next();
				//for each row, iterate the column
				Iterator<Cell> cellIterator = row.cellIterator();
				
				while (cellIterator.hasNext()){
					Cell cell=cellIterator.next();
					//check cell type and return type
					switch (cell.getCellType()){
					case Cell.CELL_TYPE_NUMERIC:
						System.out.println(cell.getNumericCellValue()+"t");
						break;
					case Cell.CELL_TYPE_STRING:
						System.out.println(cell.getStringCellValue()+"t");
						break;
					}
				}
				System.out.println("We have coconut");
			}
			file.close();
		}catch (Exception e){
			e.printStackTrace();
		}
		
	}
		

}

